﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class SalaryGateway:DBGateway
    {
        public bool IsSalaryStructureExistForThisEmployee(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblSalary where EmpId='"+empId+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Checking Employee Salary Structure");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public string SaveEmployeeSalary(int empId, Salary salary)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblSalary values(@EmpId,@Basic,@Medical,@Residence,@Transport,@PF,@Mobile,@Others,@Gross)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@Basic", SqlDbType.Decimal).Value = salary.Basic;
                CommandObj.Parameters.Add("@Medical", SqlDbType.Decimal).Value = salary.Medical;
                CommandObj.Parameters.Add("@Residence", SqlDbType.Decimal).Value = salary.Residence;
                CommandObj.Parameters.Add("@Transport", SqlDbType.Decimal).Value = salary.Transport;
                CommandObj.Parameters.Add("@PF", SqlDbType.Decimal).Value = salary.PF;
                CommandObj.Parameters.Add("@Mobile", SqlDbType.Decimal).Value = salary.Mobile;
                CommandObj.Parameters.Add("@Others", SqlDbType.Decimal).Value = salary.Others;
                CommandObj.Parameters.Add("@Gross", SqlDbType.Decimal).Value = salary.Gross;
                CommandObj.ExecuteNonQuery();
                return "Salary Sructure Save Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during saving Salary Structure");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string EditEmployeeSalary(int empId, Salary salary)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblSalary set Basic=@Basic,Medical=@Medical,Residence=@Residence,Transport=@Transport," +
                                                       "PF=@PF,Mobile=@Mobile,Others=@Others,Gross=@Gross where EmpId='"+empId+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Basic", SqlDbType.Decimal).Value = salary.Basic;
                CommandObj.Parameters.Add("@Medical", SqlDbType.Decimal).Value = salary.Medical;
                CommandObj.Parameters.Add("@Residence", SqlDbType.Decimal).Value = salary.Residence;
                CommandObj.Parameters.Add("@Transport", SqlDbType.Decimal).Value = salary.Transport;
                CommandObj.Parameters.Add("@PF", SqlDbType.Decimal).Value = salary.PF;
                CommandObj.Parameters.Add("@Mobile", SqlDbType.Decimal).Value = salary.Mobile;
                CommandObj.Parameters.Add("@Others", SqlDbType.Decimal).Value = salary.Others;
                CommandObj.Parameters.Add("@Gross", SqlDbType.Decimal).Value = salary.Gross;
                CommandObj.ExecuteNonQuery();
                return "Salary Sructure Updated Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Updating Salary Structure");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }


        public Salary GetEmployeeSalaryStructure(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblSalary where EmpId='"+empId+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                Salary salary=new Salary();
                while (reader.Read())
                {

                    salary.Id = Convert.ToInt32(reader[0]);
                    Employee employee=new Employee();
                    employee.Id = Convert.ToInt32(reader[1]);
                    salary.Employee=employee;
                    salary.Basic = Convert.ToDecimal(reader[2]);
                    salary.Medical = Convert.ToDecimal(reader[3]);
                    salary.Residence = Convert.ToDecimal(reader[4]);
                    salary.Transport = Convert.ToDecimal(reader[5]);
                    salary.PF = Convert.ToDecimal(reader[6]);
                    salary.Mobile = Convert.ToDecimal(reader[7]);
                    salary.Others = Convert.ToDecimal(reader[8]);
                    salary.Gross = Convert.ToDecimal(reader[9]);
                 }
                return salary;

            }
            catch (Exception)
            {
                throw new Exception("Error occured during Reading Salary Information, Try again");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}